#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/1/11 23:55
# @Author : 闫峰
# @File    : mysql_control.py

import datetime
import decimal
from warnings import filterwarnings
import pymysql
from typing import  Text
from util.logging_tool.log_control import ERROR
from util.readFileUtils.yamlControl import GetYamlData

# 忽略 Mysql 告警信息
filterwarnings("ignore", category=pymysql.Warning)


class MysqlDB:
    """ mysql 封装 """
    if True:

        def __init__(self, database):

            try:
                # 建立数据库连接
                self.mysql = GetYamlData().get_yaml_data()[database]
                self.conn = pymysql.connect(
                    host=self.mysql['host'],
                    port=self.mysql['port'],
                    user=self.mysql['user'],
                    password=self.mysql['password'],
                    db=self.mysql['db_name'],
                    charset='utf8'
                )

                # 使用 cursor 方法获取操作游标，得到一个可以执行sql语句，并且操作结果为字典返回的游标
                self.cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
            except AttributeError as error:
                ERROR.logger.error("数据库连接失败，失败原因 %s", error)

        def __del__(self):
            try:
                # 关闭游标
                self.cur.close()
                # 关闭连接
                self.conn.close()
            except AttributeError as error:
                ERROR.logger.error("数据库连接失败，失败原因 %s", error)

        def query(self, sql, value1, state="all1"):
            """
                查询
                :param sql:
                :param state:  all 是默认查询全部
                :return:
                """
            try:
                self.cur.execute(sql, value1)

                if state == "all":
                    # 查询全部
                    data = self.cur.fetchall()
                else:
                    # 查询单条
                    data = self.cur.fetchone()
                return data
            except AttributeError as error_data:
                ERROR.logger.error("数据库连接失败，失败原因 %s", error_data)
                raise

        def execute(self, sql: Text):
            """
                更新 、 删除、 新增
                :param sql:
                :return:
                """
            try:
                # 使用 execute 操作 sql
                rows = self.cur.execute(sql)
                # 提交事务
                self.conn.commit()
                return rows
            except AttributeError as error:
                ERROR.logger.error("数据库连接失败，失败原因 %s", error)
                # 如果事务异常，则回滚数据
                self.conn.rollback()
                raise

        @classmethod
        def sql_data_handler(cls, query_data, data):
            """
            处理部分类型sql查询出来的数据格式
            @param query_data: 查询出来的sql数据
            @param data: 数据池
            @return:
            """
            # 将sql 返回的所有内容全部放入对象中
            for key, value in query_data.items():
                if isinstance(value, decimal.Decimal):
                    data[key] = float(value)
                elif isinstance(value, datetime.datetime):
                    data[key] = str(value)
                else:
                    data[key] = value
            return data


if __name__ == '__main__':
    db = MysqlDB(database="guiji_db_erp_base")
    res = db.query(
        sql='select count(*) from lt_orderinfo where CustomerID=1 and IsStockOrder =0 and ShipStatus>0 and OrderCode = %s;',
        value1=("202209170125126555660",))
    print(res)